package com.hs.wdss.action.standard;
 
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.servlet.ModelAndView;

import com.hs.core.action.BaseAction;
import com.hs.core.db.DBMgr;
import com.hs.core.utils.PageParam;
import com.hs.core.utils.PageUtil;
import com.hs.core.utils.SystemUtil;
import com.hs.core.utils.TimeUtil;
import com.hs.wdss.model.WdssStandard;
import com.hs.wdss.service.IWdssService;
@Controller  
public class StandardController  extends BaseAction {
    /**
	 * 
	 */
	private static final long serialVersionUID = -354434172898440670L;
	private static final Logger logger = Logger.getLogger(StandardController.class);
	private IWdssService wdssService;
	@Autowired  
	public void setRoleService(IWdssService wdssService) {
		this.wdssService = wdssService;
	}

	 /**
	  * 标准数据列表
	  * @param request
	  * @param response
	  * @param modelMap
	  * @return
	  * @throws ServletException
	  * @throws IOException
	  */
	 @RequestMapping(value="/standard/standardList.do")   
     public ModelAndView dataList(HttpServletRequest request,
				HttpServletResponse response,ModelMap modelMap) throws ServletException, IOException {
		 PageParam pageParam = new  PageParam(request,"standard/standardList.do");
		 PageUtil pageUtil = null;
		 StringBuffer searchBuffer = new StringBuffer(" select a from WdssStandard a where 1=1  ");
			String category = request.getParameter("category");  
			String standardname = request.getParameter("standardname");  
			String synonyms = request.getParameter("synonyms"); 
			request.setAttribute("category", category);
			request.setAttribute("standardname", standardname);
			request.setAttribute("synonyms", synonyms);
			if(StringUtils.isNotBlank(category)){
				pageParam.getConditionMap().put("category", category);
				searchBuffer.append(" and (a.category='"+category+"') "); 
			}  
			if(StringUtils.isNotBlank(standardname)){
				pageParam.getConditionMap().put("standardname", standardname);
				searchBuffer.append(" and (a.standardname like '%"+standardname+"%') "); 
			}
			if(StringUtils.isNotBlank(synonyms)){
				pageParam.getConditionMap().put("synonyms", synonyms);
				searchBuffer.append(" and (a.synonyms like '%"+synonyms+"%') "); 
			}
			searchBuffer.append(" order by a.category");
			Map data = new HashMap();
			pageParam.setSearchSql(searchBuffer.toString());
			pageUtil = wdssService.getList(pageParam);
			logger.debug("aaaaa::::" + pageUtil.getPageList().size());
			data.put("dataList", pageUtil);     
		 return new ModelAndView("app/standard/standardlist",data);
		 
	 } 


	 /**
	  * 数据导入
	  * @param request
	  * @param response
	  * @param modelMap
	  * @return
	 * @throws Exception 
	  */
	 @RequestMapping(value="/standard/importdata.do")   
     public ModelAndView importXlsData(HttpServletRequest request,
				HttpServletResponse response,ModelMap modelMap) throws Exception {
		 MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;   
		 MultipartFile multipartFile = multipartRequest.getFile("importfile");   
		 String fileName = multipartFile.getOriginalFilename(); 
		 String webRoot = SystemUtil.getWebappRoot().toString();
		 webRoot = webRoot.replace("\\", "/") + "/temp";
		 System.out.println("webRoot::" + webRoot );
		 String sep = System.getProperty("file.separator");
		 sep = "/"; 
		 File rootDir = new File(webRoot); 
		 if (!rootDir.exists()) {
			rootDir.mkdirs(); 
		 }
		 File file = new File(webRoot + sep + fileName);
		 BufferedOutputStream bf = new BufferedOutputStream(new FileOutputStream(file));
		 bf.write(multipartFile.getBytes());
		 bf.close();
		 System.out.println("上传成功。。。。。。");
		 this.readXls(file);
	     response.getWriter().write("1");
		 return new ModelAndView("app/standard/standardlist",modelMap);
	 }
	 /**
	  * 数据修改
	  * @param request
	  * @param response
	  * @param modelMap
	  * @return
	  * @throws ServletException
	  * @throws IOException
	  */
	 @RequestMapping(value="/standard/saveData.do", method = RequestMethod.GET)   
     public ModelAndView saveData(HttpServletRequest request,
				HttpServletResponse response,ModelMap modelMap) throws ServletException, IOException {
         
		 return new ModelAndView("app/data/dataList",modelMap);
		 
	 } 
	 /**
	  * 数据删除
	  * @param request
	  * @param response
	  * @param modelMap
	  * @return
	  * @throws ServletException
	  * @throws IOException
	  */
	 @RequestMapping(value="/standard/dataDelete.do", method = RequestMethod.GET)   
     public ModelAndView dataDelete(HttpServletRequest request,
				HttpServletResponse response,ModelMap modelMap) throws ServletException, IOException {
         
		 return new ModelAndView("app/data/dataList",modelMap);
		 
	 } 
	 /**
	  * 读取excel文件
	  * @param file 要读取的excel文件
	  */
	 public void readXls(File file){
			HSSFWorkbook workbook = null;
			try {
				FileInputStream rs = new FileInputStream(file) ;      
				workbook = new HSSFWorkbook(rs);
				int total = workbook.getNumberOfSheets(); //获取sheet个数      
		        int start = 0;
		        int end = 0 ;  
				for(int i = 0 ; i < total ; i ++){  
		            HSSFSheet sheet = workbook.getSheetAt(i);// 按索引获取sheet引用          
		            System.out.println(sheet.getSheetName());      
		            start = sheet.getFirstRowNum();  //sheet起始行索引      
		            end = sheet.getLastRowNum();//sheet起结束行索引      
		            int temp = 1;      
		            HSSFRow row = null ;      
		            HSSFCell category = null ;      
		            HSSFCell standardName = null ; 
		            HSSFCell synonyms = null ;
		            HSSFCell memo = null ; 
		            
		            while(temp <= end){      
		            	 WdssStandard sta = new WdssStandard();
		                 row =  sheet.getRow(temp);//获取第N行表格数据     
		                 if(row != null){
		                	 category = row.getCell(1);//获取指定列单元格            
		                	 standardName = row.getCell(2);   
		                	 synonyms = row.getCell(3);
		                	 memo = row.getCell(4);
				               
				                 if(null != category){
				                	 sta.setCategory(getCell(category)); 
				                 }
				                 if(null != standardName){
				                	 sta.setStandardname(getCell(standardName)); 
				                 }
				                 if(null != synonyms){
				                	 sta.setSynonyms(getCell(synonyms)); 
				                 }
				                 if(null != memo){
				                	 sta.setMemo(getCell(memo)); 
				                 }
				                 sta.setImportdate(TimeUtil.getCurrentDate());
				                 StringBuffer countsql = new StringBuffer();
				                 countsql.append("select id from wdss_standard ");
				                 countsql.append(" where standardname='");
				                 countsql.append(sta.getStandardname());
				                 countsql.append("' and category='");
				                 countsql.append(sta.getCategory() + "'");
				                 String syonoyms = sta.getSynonyms();
				                 if(null != syonoyms && !"".equals(syonoyms) && !"null".equals(syonoyms)){
				                	 countsql.append(" and synonyms='" + syonoyms + "'");
				                 }
								 List accList = DBMgr.getDbUtilInst().queryList(countsql.toString(),null);  
								 int acclen = accList.size();
							     if(acclen == 0){
							    	 wdssService.saveObject(sta);
							     }else {
							    	 Map data = (Map)accList.get(0);
								     String id = data.get("id") + "";
							    	 StringBuffer usql = new StringBuffer("update wdss_standard set STANDARDNAME=?,SYNONYMS=?,CATEGORY=?,MEMO=?,IMPORTDATE=?");
							    	 usql.append(" where id=?");
							    	 Object[] obj = new Object[]{sta.getStandardname(),sta.getSynonyms(),sta.getCategory(),sta.getMemo(),sta.getImportdate(),id};
							    	 DBMgr.getDbUtilInst().update(usql.toString(), obj);
							     }
		                 }
		                 temp ++ ; 
		            } 
				}
			} catch (FileNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}  
	}
	public String getCell(HSSFCell cell){
		if(cell==null)return "";
		switch(cell.getCellType()){
			case HSSFCell.CELL_TYPE_NUMERIC:
				return (long)cell.getNumericCellValue() + "";
			case HSSFCell.CELL_TYPE_STRING:
				return cell.getStringCellValue();
			case HSSFCell.CELL_TYPE_FORMULA:
				return cell.getCellFormula();
			case HSSFCell.CELL_TYPE_BLANK:
				return "";
			case	HSSFCell.CELL_TYPE_BOOLEAN:
				return cell.getBooleanCellValue() + "";
			case HSSFCell.CELL_TYPE_ERROR:
	    	    return cell.getErrorCellValue() + "";
	    	default:
	    		return "";
		}
	}
	
	 /**
	  * 数据导出
	  * @param request
	  * @param response
	  * @return
	 * @throws Exception 
	  */
	@RequestMapping("/standard/exportData.do")   
	public ModelAndView exportData(HttpServletRequest request,
			HttpServletResponse response) throws Exception { 
		try  {
			String path = this.exportData();
			System.out.println("message::" + path );
			//  path是指欲下载的文件的路径。 
			response.setCharacterEncoding("UTF-8");
			File file  =   new  File(path);
			String filename  =  file.getName();
			int fileLength = (int)file.length();
			int                 length   = 0;
	        ServletOutputStream op       = response.getOutputStream();
	        ServletContext      context  = request.getSession().getServletContext();
	        String              mimetype = context.getMimeType( filename );
	        byte[]   byteContents   =   new   byte[fileLength];   
	        FileInputStream fis   =   new   FileInputStream(file);   
	        int   retcd   =   fis.read(byteContents);   
	        fis.close();    
	        logger.debug("mimetype:" + mimetype);
	        response.setContentType(mimetype == null?"application/vnd.ms-excel":mimetype );
	        response.setContentLength( retcd );
	        response.setHeader( "Content-Disposition", "attachment; filename=\"" + new String(filename.getBytes("gb2312"),"ISO-8859-1") + "\"" );
	        op.write(byteContents,0,retcd); 
	        op.close();
     }  catch  (IOException ex) {
         ex.printStackTrace();
     }
		return null;  
	} 
	/**
	 * 生成题录数据文件
	 * @return 生成的文件的路径
	 */
	public String exportData(){
		String message = "false";
		 try {
				HSSFWorkbook wb = new HSSFWorkbook(); 
				 HSSFSheet sheet = wb.createSheet("new sheet"); 
				 HSSFRow row = sheet.createRow((short)0); 
				 row.createCell(0).setCellValue("序号"); 
				 row.createCell(1).setCellValue("类别"); 
				 row.createCell(2).setCellValue("正名"); 
				 row.createCell(3).setCellValue("异名"); 
				 row.createCell(4).setCellValue("描述"); 
				 CellStyle cs=wb.createCellStyle();  
				 List accList = DBMgr.getDbUtilInst().queryList("select * from wdss_standard order by id",null);
				 for(int i=0; i<accList.size(); i++){
						Map data = (Map)accList.get(i);
						HSSFRow onerow = sheet.createRow((short)(i+1)); 
						onerow.createCell(0).setCellValue(String.valueOf(data.get("id"))); 
						onerow.createCell(1).setCellValue((String)data.get("category")); 
						onerow.createCell(2).setCellValue((String)data.get("standardname")); 
						onerow.createCell(3).setCellValue((String)data.get("synonyms")); 
						onerow.createCell(4).setCellValue((String)data.get("memo")); 
				 }	
				 String tempFilepath = SystemUtil.getWebappRoot().toString();
				 tempFilepath = tempFilepath.replace("\\", "/") + "/temp";
				 System.out.println("tempFilepath::" + tempFilepath );
				 File tempfile = new File(tempFilepath);
				 if(!tempfile.exists()){
					 tempfile.mkdirs();
				 }
				 String destfile = tempFilepath + "/" + "标准数据.xls";
				 FileOutputStream fileOut = new FileOutputStream(destfile); 
				 wb.write(fileOut); 
				 fileOut.close();
				 message = destfile;
			} catch (FileNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} 
		return message;
		
	}
}
